USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[ResultThrowList]    スクリプト日付: 01/26/2026 08:32:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultThrowList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultThrowList]
GO

CREATE PROCEDURE [dbo].[ResultThrowList]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@KUMINO int,
	@KYOGISYAFLG int,
	@SYUMOKUKUBUN int,
	@SUBSYUMOKUKUBUN int,
	@SUBKIROKUKUBUN int,
	@SORTFLG int
AS
SELECT 
	P.LANE, 
	P.KYOGISYANO, 
	P.NUMBER, 
	P.SEINEN, 
	P.GAKUNEN, 
	P.KYOGISYAMEI, 
	P.SYOZOKUMEI,
	P.SYOZOKUTIMEI,
	dbo.Kaze(@SYUMOKUKUBUN, @SUBSYUMOKUKUBUN, (CASE WHEN P.RKIROKU = 0 THEN '' ELSE dbo.Kiroku(P.RKIROKU,@SUBSYUMOKUKUBUN,@SUBKIROKUKUBUN,P.EXTEND2)END), P.EXTEND1 ) AS RKIROKU,
	dbo.Jyuni((CASE WHEN P.JYUNI = 0 OR P.JYUNI IS NULL THEN '' ELSE STR(P.JYUNI)END) ,P.OPENFLG) AS JYUNI,
	P.Sort,
	P.EXTEND3, 
	dbo.CommentCodeConvert(P.RCOMMENT, P.SYUKKETU) AS RCOMMENT,
	P.EXTEND1, 
	P.SYUKKETU, 
	P.OPENFLG, 
	P.EXTEND2, 
	P.EXTEND4, 
	P.PLUS,
	P.SYOZOKUNO, 
	P.SYOZOKUTINO, 
	P.EXTEND5,
	P.KYOGIFLG,
	P.KINOUFLG,
	P.KHOST,
	P.ROLLCALLFLG,
	P.KUMIWAKEFLG,  
	P.RHOST,
	MAX(CASE SIGINO WHEN 1 THEN P.KIROKU ELSE '' END) AS [1],
	MAX(CASE SIGINO WHEN 2 THEN P.KIROKU ELSE '' END) AS [2],
	MAX(CASE SIGINO WHEN 3 THEN P.KIROKU ELSE '' END) AS [3],
	MAX(CASE SIGINO WHEN 4 THEN P.KIROKU ELSE '' END) AS [4],
	MAX(CASE SIGINO WHEN 5 THEN P.KIROKU ELSE '' END) AS [5],
	MAX(CASE SIGINO WHEN 6 THEN P.KIROKU ELSE '' END) AS [6],
	P.PREKIROKU, -- 202601 追加 資格記録
	P.KPREKIROKU,
	P.KSE_EXTEND8 -- 202601 追加 イエローカード
FROM
	(
		SELECT DISTINCT
			LA.LANE, 
			KS.KYOGISYANO, 
			(KS.NUMBER1 + CONVERT(varchar, KS.NUMBER2)) AS NUMBER, 
			KS.SEINEN, 
			KS.GAKUNEN, 
			(CONVERT(varchar, KS.KYOGISYAKANA) + '<br/>' + CONVERT(varchar, KS.KYOGISYAMEI)) AS KYOGISYAMEI, 
			(CONVERT(varchar, S.SYOZOKUMEI) + '<br/>' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI)) AS SYOZOKUMEI,
			ST.SYOZOKUTIMEI,
			RE.RKIROKU,
			dbo.Kaze(@SYUMOKUKUBUN, @SUBSYUMOKUKUBUN, dbo.Kiroku(RS.KIROKU,@SUBSYUMOKUKUBUN,@SUBKIROKUKUBUN, RS.EXTEND2), RS.KAZE) AS KIROKU,
			RE.JYUNI,
			RE.JYUNI AS Sort,
			RE.EXTEND3, 
			RE.RCOMMENT,
			RE.EXTEND1, 
			LA.SYUKKETU, 
			SK.OPENFLG, 
			SK.KIROKU AS PREKIROKU,  
			RE.EXTEND2, 
			RE.EXTEND4, 
			dbo.Passage(RE.PLUS) AS PLUS,
			S.SYOZOKUNO, 
			ST.SYOZOKUTINO, 
			RE.EXTEND5,		
			RS.SIGINO,
			TKU.KYOGIFLG,
			TKU.KINOUFLG,
			TKU.HOST AS KHOST,
			TKU.ROLLCALLFLG,
			TR.KUMIWAKEFLG,  
			TR.HOST AS RHOST,
			KSE.EXTEND8 AS KSE_EXTEND8,
			KONSEI.KIROKU AS KPREKIROKU 
		FROM
			TBL_KUMI AS TKU, 
			TBL_ROUND AS TR,
			TBL_LANE AS LA 
			FULL OUTER JOIN 
			(
				SELECT DISTINCT 
					RE.LANE,
					RE.KUMINO,
					RE.JYUNI,
					RE.RCOMMENT,
					RE.EXTEND1,
					RE.EXTEND2, 
					RE.EXTEND3, 
					RE.EXTEND4, 
					RE.EXTEND5,
					RE.KIROKU AS RKIROKU, 
					RE.PLUS, 
					RE.RANK, 
					RE.SANKOKIROKU
				FROM TBL_LANE AS LA, 
					TBL_RESULT AS RE
				WHERE LA.TAIKAINO = @TAIKAINO
					AND LA.KYOGINO = @KYOGINO
					AND LA.ROUNDNO = @ROUNDNO
					AND LA.SUBNO = @SUBNO
					AND LA.KUMINO = @KUMINO
					AND LA.TAIKAINO = RE.TAIKAINO 
					AND LA.KYOGINO = RE.KYOGINO 
					AND LA.SUBNO = RE.SUBNO 
					AND LA.ROUNDNO = RE.ROUNDNO 
					AND LA.KUMINO = RE.KUMINO 
			)AS RE 
			ON LA.LANE = RE.LANE AND LA.KUMINO = RE.KUMINO 
			LEFT OUTER JOIN
			(
				SELECT
					LA.LANE, 
					RS.KIROKU,
					RS.SIGINO,
					RE.EXTEND2, 
					RS.EXTEND1 AS KAZE
				FROM
					TBL_LANE AS LA, 
					TBL_RESULT AS RE,
					TBL_RESULT_SIGI AS RS 
				WHERE
					LA.TAIKAINO = RE.TAIKAINO 
					AND LA.KYOGINO = RE.KYOGINO 
					AND LA.SUBNO = RE.SUBNO 
					AND LA.ROUNDNO = RE.ROUNDNO 
					AND LA.KUMINO = RE.KUMINO 
					AND LA.LANE = RE.LANE
					AND LA.TAIKAINO = RS.TAIKAINO
					AND LA.KYOGINO = RS.KYOGINO
					AND LA.SUBNO = RS.SUBNO
					AND LA.ROUNDNO = RS.ROUNDNO
					AND LA.KUMINO = RS.KUMINO
					AND LA.LANE = RS.LANE
					AND LA.TAIKAINO = @TAIKAINO
					AND LA.KYOGINO = @KYOGINO
					AND LA.ROUNDNO = @ROUNDNO
					AND LA.SUBNO = @SUBNO
					AND LA.KUMINO = @KUMINO
			) AS RS ON LA.LANE = RS.LANE 
			LEFT JOIN TBL_KYOGI AS KYOGI --20140514
				ON LA.TAIKAINO = KYOGI.TAIKAINO--20140514
				AND LA.KYOGINO = KYOGI.KYOGINO --20140514
				INNER JOIN TBL_SYUMOKU_MASTER AS SYUMO --20140514
					ON KYOGI.SYUMOKUNO =SYUMO.SYUMOKUNO
			LEFT JOIN TBL_KYOGISYA AS KS ON LA.TAIKAINO = KS.TAIKAINO 
			LEFT JOIN TBL_KYOGISYA_EXTE AS KSE on KS.TAIKAINO = KSE.TAIKAINO AND KS.KYOGISYANO = KSE.KYOGISYANO
			LEFT JOIN TBL_KONSEI_MASTER AS KM
				ON KM.SYUMOKUNO = KYOGI.SYUMOKUNO   
				AND KM.KONSEINO = @SUBNO
			LEFT JOIN TBL_KYOGISYA_KONSEI_UCHIWAKE_KIROKU as KONSEI 
				ON LA.TAIKAINO = KONSEI.TAIKAINO AND LA.KYOGINO = KONSEI.KYOGINO 
				AND KONSEI.SYUMOKUNO = KM.KONSEINO AND KONSEI.KYOGISYANO = KS.KYOGISYANO,
			TBL_SYOZOKU AS S, 
			TBL_SYOZOKUTI AS ST, 
			TBL_SANKAKYOGI AS SK
		WHERE
			LA.TAIKAINO = KS.TAIKAINO 
			AND LA.KYOGISYANO = KS.KYOGISYANO 
			AND LA.TAIKAINO = SK.TAIKAINO 
			AND LA.KYOGISYANO = SK.KYOGISYANO 
			AND LA.KYOGINO = SK.KYOGINO 
			AND KS.TAIKAINO = S.TAIKAINO 
			AND KS.SYOZOKUNO1 = S.SYOZOKUNO 
			AND S.SYOZOKUTINO = ST.SYOZOKUTINO 
			AND LA.TAIKAINO = @TAIKAINO
			AND LA.KYOGINO = @KYOGINO
			AND LA.ROUNDNO = @ROUNDNO
			AND LA.SUBNO = @SUBNO
			AND LA.KUMINO = @KUMINO
			AND KS.KYOGISYAFLG = @KYOGISYAFLG
			AND TKU.TAIKAINO = LA.TAIKAINO
			AND TKU.KYOGINO = LA.KYOGINO
			AND TKU.SUBNO = LA.SUBNO
			AND TKU.KUMINO = LA.KUMINO
			AND TKU.ROUNDNO = LA.ROUNDNO
			AND LA.TAIKAINO = KYOGI.TAIKAINO--20140514
			AND LA.KYOGINO = KYOGI.KYOGINO --20140514
			AND KYOGI.SYUMOKUNO =SYUMO.SYUMOKUNO
			AND TR.TAIKAINO = @TAIKAINO 
			AND TR.KYOGINO = @KYOGINO 
			AND TR.SUBNO = @SUBNO 
			AND TR.ROUNDNO = @ROUNDNO
	) AS P --20140514

GROUP BY 
	P.LANE, P.KYOGISYANO, 
	P.NUMBER, P.SEINEN, 
	P.GAKUNEN, P.KYOGISYAMEI, 
	P.SYOZOKUMEI, P.SYOZOKUTIMEI,
	P.RKIROKU, P.JYUNI,
	P.Sort, P.EXTEND3, 
	P.RCOMMENT, P.EXTEND1, 
	P.SYUKKETU, P.OPENFLG, 
	P.EXTEND2, P.EXTEND4, 
	P.PLUS, P.SYOZOKUNO, 
	P.SYOZOKUTINO, P.KYOGIFLG,
	P.KINOUFLG, P.KHOST,
	P.ROLLCALLFLG, P.KUMIWAKEFLG,  
	P.RHOST, P.EXTEND5,P.PREKIROKU,P.KPREKIROKU,
	P.KSE_EXTEND8 
ORDER BY  
	CASE
		WHEN @SORTFLG =  1 THEN 
			CASE WHEN P.SYUKKETU = 2 THEN 1 ELSE 0 END
		WHEN @SORTFLG =  1 THEN 
			CASE WHEN P.OPENFLG <> 0 THEN 1 ELSE 0 END
	END ASC, 
	CASE
		WHEN @SORTFLG =  1 THEN 
			CASE WHEN P.Sort IS NULL OR P.Sort = 0 THEN P.LANE
				WHEN P.Sort IS NULL OR P.Sort = 0 THEN 1 
			END
	END ASC, 
	CASE
		WHEN @SORTFLG =  1 THEN P.Sort
	END ASC,
	P.LANE ASC